<?php
/**
 * 信息管理MODEL
 * User: Administrator
 * Date: 2016/7/8 0008
 * Time: 上午 10:52
 */

namespace app\models;

use Yii;
use app\librarys\DbCacheUtility;
use app\librarys\Tools;

class Information
{

    /**
     * 获取机构列表（包括0机构）
     * @return array
     */
    public static function getAllInstitution()
    {
        //查询SQL
        $sql = "select case when min(parent_code)='0' then '0' else '0' end as code,'系统平台' as name,'' as parent_code from t_institution where parent_code = '0'
                UNION select code,name,parent_code from t_institution ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        //执行获取结果
        $inst_0 = $cmd->queryAll();
        return $inst_0;

    }

    /*************广告************************************************************************************************/
    /**
     * 查询广告总数
     * @param string $institution_code
     * @param string $name
     * @param int $is_shown [0/1]
     * @return false|null|string
     */
    public static function getAdCountByfilters($institution_code, $name, $is_shown)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {
            $filterSQL = '';
            //添加查询条件
            if ($name != null && $name != '') {
                $filterSQL = $filterSQL . " and name like :filter_name ";
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $filterSQL = $filterSQL . " and is_shown = :filter_is_shown ";
            }
            //查询SQL
            $sql = "select count(id) from t_ad where institution_code=:institution_code $filterSQL ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            //绑定参数
            if ($name != null && $name != '') {
                $filter_name = '%' . $name . '%';
                $cmd->bindValue(':filter_name', $filter_name);
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $cmd->bindValue(':filter_is_shown', $is_shown);
            }
            //执行获取结果
            $result = $cmd->queryScalar();
        }
        return $result;
    }

    /**
     * 查询广告列表
     * @param string $institution_code
     * @param string $name
     * @param int $is_shown [0/1]
     * @param int $page
     * @param int $pagesize
     * @return array|null
     */
    public static function getAdListByfilters($institution_code, $name, $is_shown, $page = 1, $pagesize = 10)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {
            $filterSQL = '';

            //添加查询条件
            if ($name != null && $name != '') {
                $filterSQL = $filterSQL . " and t.name like :filter_name ";
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $filterSQL = $filterSQL . " and t.is_shown = :filter_is_shown ";
            }

            //计算起始数据行
            if ($page <= 0) $page = 0;
            $start = ($page - 1) * $pagesize;
            //查询SQL
            $sql = "select t.*,ac.name as category_name from t_ad t left join t_ad_category ac on ac.id = t.category_id where t.institution_code=:institution_code $filterSQL LIMIT $start,$pagesize ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            //绑定参数
            if ($name != null && $name != '') {
                $filter_name = '%' . $name . '%';
                $cmd->bindValue(':filter_name', $filter_name);
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $cmd->bindValue(':filter_is_shown', $is_shown);
            }
            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 根据机构编号和广告ID查询广告详情
     * @param int $id
     * @param string $institution_code
     * @return array
     */
    public static function getAdDetails($id, $institution_code)
    {
        //查询SQL
        $sql = "select t.*,i.name as institution_name from t_ad t left join t_institution i on i.code = t.institution_code where t.id=:id and t.institution_code=:institution_code ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':institution_code', $institution_code);
        //执行获取结果
        $inst_0 = $cmd->queryAll();
        if ($inst_0 != false) {
            return $inst_0[0];
        } else {
            return $inst_0;
        }
    }

    /**
     * 广告数据新增 修改数据保存 （当$id为空是默认为新增 不为空时为修改）
     * @param int $id [null/int]
     * @param string $institution_code
     * @param string $name
     * @param int $category_id
     * @param string $image_url
     * @param string $link_url
     * @param string $description
     * @param int $is_shown [0/1]
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function adSave($id, $institution_code, $name, $category_id, $image_url, $link_url, $description, $is_shown)
    {
        $sql = "call p_save_ad(:in_id,:in_institution_code,:in_name,:in_category_id,:in_image_url,:in_link_url,:in_description,:in_is_shown,@oid)";

        $cmd = \Yii::$app->db->createCommand($sql);
        if ($id == null || $id == '') {
            $id = '0';
        }
        $cmd->bindValue(':in_id', $id);
        $cmd->bindValue(':in_institution_code', $institution_code);
        $cmd->bindValue(':in_name', $name);
        $cmd->bindValue(':in_category_id', $category_id);
        $cmd->bindValue(':in_image_url', $image_url);
        $cmd->bindValue(':in_link_url', $link_url);
        $cmd->bindValue(':in_description', $description);
        $cmd->bindValue(':in_is_shown', $is_shown);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @oid');
        $oid = $cmd->queryScalar();

        return $oid;

    }

    /**
     * 根据广告ID删除数据
     * @param int $id
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function adDelete($id)
    {
        if ($id != null && $id != '') {
            $cmd = \Yii::$app->db->createCommand('delete from t_ad where id=:id');
            $cmd->bindValue(':id', $id);
            return $cmd->execute();
        } else {
            return false;
        }
    }



    /*************广告分类********/
    /**
     * 查询广告分类列表(机构分类与系统平台分类)
     * @param string $institution_code
     * @return array|null
     */
    public static function getAdCategoryAndSYSList($institution_code)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "";
            if ($institution_code != '0') {
                $sql = "select * from t_ad_category where institution_code = '0' or institution_code=:institution_code ";
            } else {
                $sql = "select * from t_ad_category where institution_code=:institution_code ";
            }


            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 查询广告分类列表
     * @param string $institution_code
     * @return array|null
     */
    public static function getAdCategoryList($institution_code)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "select * from t_ad_category where institution_code='0' or institution_code=:institution_code ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 根据分类定义标识符  或 广告分类名称 查询广告分类
     * @param string $institution_code
     * @param string|null $id
     * @param string $identifier_code
     * @return array|null
     */
    public static function getAdCategoryByKey($institution_code, $id, $identifier_code)
    {

        $result = null;
        if ($identifier_code != null && $identifier_code != '') {
            $filterSQL = '';

            //添加查询条件
            if ($id != null && $id != '') {
                $filterSQL = $filterSQL . " and id !=:filter_id ";
            }

            //查询SQL
            $sql = "select * from t_ad_category where (institution_code='0' or institution_code=:institution_code) and identifier_code=:identifier_code $filterSQL";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            $cmd->bindValue(':identifier_code', $identifier_code);

            if ($id != null && $id != '') {
                $cmd->bindValue(':filter_id', $id);
            }
            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 根据机构编号和广告分类ID查询分类详情
     * @param int $id
     * @param string $institution_code
     * @return array
     */
    public static function getAdCategoryDetails($id, $institution_code)
    {
        //查询SQL
        $sql = "select t.*,i.name as institution_name from t_ad_category t left join t_institution i on i.code = t.institution_code where t.id=:id and t.institution_code=:institution_code ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':institution_code', $institution_code);
        //执行获取结果
        $inst_0 = $cmd->queryAll();
        if ($inst_0 != false) {
            return $inst_0[0];
        } else {
            return $inst_0;
        }
    }

    /**
     * 广告分类数据新增 修改数据保存 （当$id为空是默认为新增 不为空时为修改）
     * @param int $id [null/int]
     * @param string $institution_code
     * @param string $name
     * @param string $identifier_code
     * @param text $description
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function adCategorySave($id, $institution_code, $name, $identifier_code, $description)
    {
        $sql = "call p_save_ad_category(:in_id,:in_institution_code,:in_name,:in_identifier_code,:in_description,@oid)";

        $cmd = \Yii::$app->db->createCommand($sql);
        if ($id == null || $id == '') {
            $id = '0';
        }
        $cmd->bindValue(':in_id', $id);
        $cmd->bindValue(':in_institution_code', $institution_code);
        $cmd->bindValue(':in_name', $name);
        $cmd->bindValue(':in_identifier_code', $identifier_code);
        $cmd->bindValue(':in_description', $description);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @oid');
        $oid = $cmd->queryScalar();

        return $oid;

    }

    /**
     * 返回当前机构中名称相同的广告设置数量
     * @param $institution_code
     * @param $name
     * @return false|null|string
     */
    public static function getSameNameCategoryCount($institution_code, $name, $id=null)
    {
        $filterSQL = '';
        //添加查询条件
        if ($id != null && $id != '') {
            $filterSQL = $filterSQL . " and id !=:filter_id ";
        }
        $sql = "SELECT count(1) FROM t_ad_category WHERE institution_code = :institution_code AND name = :name $filterSQL";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':name', $name);
        if ($id != null && $id != '') {
            $cmd->bindValue(':filter_id', $id);
        }
        $result = $cmd->queryScalar();
        return $result;
    }

    /**
     * 根据广告分类ID删除数据
     * @param int $id
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function adCategoryDelete($id)
    {
        if ($id != null && $id != '') {
            $cmd = \Yii::$app->db->createCommand('delete from t_ad_category where id=:id');
            $cmd->bindValue(':id', $id);
            return $cmd->execute();
        } else {
            return false;
        }
    }
    /*************广告分类********/

    /**
     * 根据机构编码复制上级机构的广告分类及广告设置
     * @param $instcode
     * @return bool|int
     * @throws \yii\db\Exception
     */
    public static function copyAdAndCategoryByInstCode($instcode)
    {
        $transaction = \Yii::$app->db->beginTransaction();
        $status = 0;
        if ($instcode != null && $instcode != '') {
            //  var_dump("instcode=".$instcode);
            try {
//                $sql = "call p_syn_institution_ad_category(:in_institution_code,@ostatus)";
//                $cmd = \Yii::$app->db->createCommand($sql);
//                $cmd->bindValue(':in_institution_code', $instcode);
//                $cmd->execute();
//
//                $cmd = \Yii::$app->db->createCommand('SELECT @ostatus');
//                $ostatus = $cmd->queryScalar();

                // 查询分类列表
                $sql = "select name,identifier_code,description from t_ad_category where institution_code != '0'
                         and institution_code = (select parent_code from t_institution where code =:institution_code)";
                //创建查询
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':institution_code', $instcode);
                //执行获取结果
                $adcategorylist = $cmd->queryAll();
                if ($adcategorylist != null && $adcategorylist != '') {

                    foreach ($adcategorylist as $item) {
                        /// 查询广告分类在机构下是否存在
                        $sql4 = "select name  from t_ad_category where name =:name and institution_code =:institution_code";
                        $cmd = \Yii::$app->db->createCommand($sql4);
                        $cmd->bindValue(':name', $item['name']);
                        $cmd->bindValue(':institution_code', $instcode);
                        //执行获取结果
                        $adcategory = $cmd->queryAll();

                        if ($adcategory == null || $adcategory == '') {
                            $id = Tools::db_uuid();
                            $cmd = \Yii::$app->db->createCommand('insert into t_ad_category
                                  (id,name,identifier_code,institution_code,description) VALUES
                                  (:id, :name, :identifier_code, :institution_code, :description)');
                            $cmd->bindValue(':id', $id);
                            $cmd->bindValue(':name', $item['name']);
                            $cmd->bindValue(':identifier_code', $item['identifier_code']);
                            $cmd->bindValue(':institution_code', $instcode);
                            $cmd->bindValue(':description', $item['description']);
                            $cmd->execute();
                        }
                    }
                }

                $ostatus = 0;

                if ($ostatus == 0) {

//                    $sql = "call p_syn_institution_ad(:in_institution_code,@ostatus)";
//                    $cmd = \Yii::$app->db->createCommand($sql);
//                    $cmd->bindValue(':in_institution_code', $instcode);
//                    $cmd->execute();

                    //查询广告列表
                    $sql = "select t.name,t.image_url,t.link_url,t.description,t.is_shown,t.category_id from t_ad t
                            where t.institution_code = (select parent_code from t_institution where code =:institution_code)";
                    //创建查询
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':institution_code', $instcode);
                    //执行获取结果
                    $adlist = $cmd->queryAll();

                    if ($adlist != null && $adlist != '') {
                        for ($iii = 0; $iii < count($adlist); $iii++) {

                            $item = $adlist[$iii];
                            ///查询广告分类所属机构编码 及 新机构分类ID
                            $sql2 = "select t.institution_code ,(select id from t_ad_category where name = t.name and institution_code =:institution_code) as inst_category_id
                            from t_ad_category t where t.id =:category_id";
                            $cmd = \Yii::$app->db->createCommand($sql2);
                            $cmd->bindValue(':institution_code', $instcode);
                            $cmd->bindValue(':category_id', $item['category_id']);
                            //执行获取结果
                            $category = $cmd->queryAll();

                            $new_category_id = "";

                            if ($category != null && $category != '') {
                                if ($category[0]['institution_code'] == 0) {
                                    $new_category_id = $item['category_id'];
                                } else {
                                    $new_category_id = $category[0]['inst_category_id'];
                                }
                            }

                            /// 查询广告在机构下是否存在
                            $sql3 = "select name  from t_ad where name =:name and institution_code =:institution_code";
                            $cmd = \Yii::$app->db->createCommand($sql3);
                            $cmd->bindValue(':name', $item['name']);
                            $cmd->bindValue(':institution_code', $instcode);
                            //执行获取结果
                            $ad = $cmd->queryAll();


                            if ($new_category_id != null && $new_category_id != '' && ($ad == null || $ad == '')) {
                                $id = Tools::db_uuid();

                                $sql = 'insert into t_ad
                                  (id,institution_code,`name`,image_url,link_url,description,is_shown,category_id,create_datetime) VALUES
                                  (:id, :institution_code,:name,:image_url,:link_url, :description, :is_shown, :category_id, NOW())';
                                $cmd = \Yii::$app->db->createCommand($sql);
                                $cmd->bindValue(':id', $id);
                                $cmd->bindValue(':institution_code', $instcode);
                                $cmd->bindValue(':name', $item['name']);
                                $cmd->bindValue(':image_url', $item['image_url']);
                                $cmd->bindValue(':link_url', $item['link_url']);
                                $cmd->bindValue(':description', $item['description']);
                                $cmd->bindValue(':is_shown', $item['is_shown']);
                                $cmd->bindValue(':category_id', $new_category_id);
                                $rtv = $cmd->execute();

                            }
                        }
                    }

                }
                $transaction->commit();
            } catch (\Exception $e) {
                $transaction->rollBack();
                $status = 1;
            }
        }

        return $status;
    }
    /*************广告************************************************************************************************/

    /*************新闻咨询********************************************************************************************/
    /**
     * 查询新闻咨询总数
     * @param string $institution_code
     * @param string $title
     * @param int $is_shown [0/1]
     * @return false|null|string
     */
    public static function getNewsCountByfilters($institution_code, $title, $is_shown)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {
            $filterSQL = '';
            //添加查询条件
            if ($title != null && $title != '') {
                $filterSQL = $filterSQL . " and title like :filter_title ";
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $filterSQL = $filterSQL . " and is_shown = :filter_is_shown ";
            }
            //查询SQL
            $sql = "select count(id) from t_news where institution_code=:institution_code $filterSQL ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            //绑定参数
            if ($title != null && $title != '') {
                $filter_title = '%' . $title . '%';
                $cmd->bindValue(':filter_title', $filter_title);
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $cmd->bindValue(':filter_is_shown', $is_shown);
            }
            //执行获取结果
            $result = $cmd->queryScalar();
        }
        return $result;
    }

    /**
     * 查询新闻咨询列表
     * @param string $institution_code
     * @param string $title
     * @param int $is_shown [0/1]
     * @param int $page
     * @param int $pagesize
     * @return array|null
     */
    public static function getNewsListByfilters($institution_code, $title, $is_shown, $page = 1, $pagesize = 10)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {
            $filterSQL = '';

            //添加查询条件
            if ($title != null && $title != '') {
                $filterSQL = $filterSQL . " and t.title like :filter_title ";
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $filterSQL = $filterSQL . " and t.is_shown = :filter_is_shown ";
            }

            //计算起始数据行
            if ($page <= 0) $page = 0;
            $start = ($page - 1) * $pagesize;
            //查询SQL
            $sql = "select t.*,ac.name as category_name from t_news t left join t_news_category ac on ac.id = t.category_id where t.institution_code=:institution_code $filterSQL LIMIT $start,$pagesize ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            //绑定参数
            if ($title != null && $title != '') {
                $filter_title = '%' . $title . '%';
                $cmd->bindValue(':filter_title', $filter_title);
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $cmd->bindValue(':filter_is_shown', $is_shown);
            }
            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 根据机构编号和新闻咨询ID查询详情
     * @param int $id
     * @param string $institution_code
     * @return array
     */
    public static function getNewsDetails($id, $institution_code)
    {
        //查询SQL
        $sql = "select * from t_news where id=:id and institution_code=:institution_code ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':institution_code', $institution_code);
        //执行获取结果
        $inst_0 = $cmd->queryAll();
        if ($inst_0 != false) {
            return $inst_0[0];
        } else {
            return $inst_0;
        }
    }

    /**
     * 新闻咨询数据新增 修改数据保存 （当$id为空是默认为新增 不为空时为修改）
     * @param int $id [null/int]
     * @param string $institution_code
     * @param string $title
     * @param int $category_id
     * @param string $content_html
     * @param string $description
     * @param int $is_shown [0/1]
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function newsSave($id, $institution_code, $title, $category_id, $content_html, $description, $is_shown)
    {
        $sql = "call p_save_news(:in_id,:in_institution_code,:in_title,:in_category_id,:in_content_html,:in_description,:in_is_shown,@oid)";

        $cmd = \Yii::$app->db->createCommand($sql);
        if ($id == null || $id == '') {
            $id = '0';
        }
        $cmd->bindValue(':in_id', $id);
        $cmd->bindValue(':in_institution_code', $institution_code);
        $cmd->bindValue(':in_title', $title);
        $cmd->bindValue(':in_category_id', $category_id);
        $cmd->bindValue(':in_content_html', $content_html);
        $cmd->bindValue(':in_description', $description);
        $cmd->bindValue(':in_is_shown', $is_shown);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @oid');
        $oid = $cmd->queryScalar();

        return $oid;

    }

    /**
     * 根据新闻咨询ID删除数据
     * @param int $id
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function newsDelete($id)
    {
        if ($id != null && $id != '') {
            $cmd = \Yii::$app->db->createCommand('delete from t_news where id=:id');
            $cmd->bindValue(':id', $id);
            return $cmd->execute();
        } else {
            return false;
        }
    }

    /*************新闻咨询分类********/
    /**
     * 查询新闻咨询分类列表(机构分类与系统平台分类)
     * @param string $institution_code
     * @return array|null
     */
    public static function getNewsCategoryAndSYSList($institution_code)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "";
            if ($institution_code != '0') {
                $sql = "select * from t_news_category where institution_code = '0' or institution_code=:institution_code ";
            } else {
                $sql = "select * from t_news_category where institution_code=:institution_code ";
            }
            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 查询新闻咨询分类列表
     * @param string $institution_code
     * @return array|null
     */
    public static function getNewsCategoryList($institution_code)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "select * from t_news_category where institution_code='0' or institution_code=:institution_code ";
            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 根据机构编号和新闻咨询分类ID查询分类详情
     * @param int $id
     * @param string $institution_code
     * @return array
     */
    public static function getNewsCategoryDetails($id, $institution_code)
    {
        //查询SQL
        $sql = "select * from t_news_category  where id=:id and institution_code=:institution_code ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':institution_code', $institution_code);
        //执行获取结果
        $inst_0 = $cmd->queryAll();
        if ($inst_0 != false) {
            return $inst_0[0];
        } else {
            return $inst_0;
        }
    }

    /**
     * 新闻咨询分类数据新增 修改数据保存 （当$id为空是默认为新增 不为空时为修改）
     * @param int $id [null/int]
     * @param string $institution_code
     * @param string $name
     * @param string $identifier_code
     * @param text $description
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function newsCategorySave($id, $institution_code, $name, $identifier_code, $description)
    {
        $sql = "call p_save_news_category(:in_id,:in_institution_code,:in_name,:in_identifier_code,:in_description,@oid)";

        $cmd = \Yii::$app->db->createCommand($sql);
        if ($id == null || $id == '') {
            $id = '0';
        }
        $cmd->bindValue(':in_id', $id);
        $cmd->bindValue(':in_institution_code', $institution_code);
        $cmd->bindValue(':in_name', $name);
        $cmd->bindValue(':in_identifier_code', $identifier_code);
        $cmd->bindValue(':in_description', $description);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @oid');
        $oid = $cmd->queryScalar();

        return $oid;

    }

    /**
     * 根据新闻咨询分类ID删除数据
     * @param int $id
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function newsCategoryDelete($id)
    {
        if ($id != null && $id != '') {
            $cmd = \Yii::$app->db->createCommand('delete from t_news_category where id=:id');
            $cmd->bindValue(':id', $id);
            return $cmd->execute();
        } else {
            return false;
        }
    }
    /*************新闻咨询分类********/
    /*************新闻咨询********************************************************************************************/

    /*************通知公告********************************************************************************************/
    /**
     * 查询通知公告总数
     * @param string $institution_code
     * @param string $title
     * @param int $is_shown [0/1]
     * @return false|null|string
     */
    public static function getNoticeCountByfilters($institution_code, $title, $is_shown)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {
            $filterSQL = '';
            //添加查询条件
            if ($title != null && $title != '') {
                $filterSQL = $filterSQL . " and title like :filter_title ";
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $filterSQL = $filterSQL . " and is_shown = :filter_is_shown ";
            }
            //查询SQL
            $sql = "select count(id) from t_notice where institution_code=:institution_code $filterSQL ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            //绑定参数
            if ($title != null && $title != '') {
                $filter_title = '%' . $title . '%';
                $cmd->bindValue(':filter_title', $filter_title);
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $cmd->bindValue(':filter_is_shown', $is_shown);
            }
            //执行获取结果
            $result = $cmd->queryScalar();
        }
        return $result;
    }

    /**
     * 查询通知公告列表
     * @param string $institution_code
     * @param string $title
     * @param int $is_shown [0/1]
     * @param int $page
     * @param int $pagesize
     * @return array|null
     */
    public static function getNoticeListByfilters($institution_code, $title, $is_shown, $page = 1, $pagesize = 10)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {
            $filterSQL = '';

            //添加查询条件
            if ($title != null && $title != '') {
                $filterSQL = $filterSQL . " and t.title like :filter_title ";
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $filterSQL = $filterSQL . " and t.is_shown = :filter_is_shown ";
            }

            //计算起始数据行
            if ($page <= 0) $page = 0;
            $start = ($page - 1) * $pagesize;
            //查询SQL
            $sql = "select t.*,ac.name as category_name from t_notice t left join t_notice_category ac on ac.id = t.category_id where t.institution_code=:institution_code $filterSQL LIMIT $start,$pagesize ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            //绑定参数
            if ($title != null && $title != '') {
                $filter_title = '%' . $title . '%';
                $cmd->bindValue(':filter_title', $filter_title);
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $cmd->bindValue(':filter_is_shown', $is_shown);
            }
            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 根据机构编号和通知公告ID查询详情
     * @param int $id
     * @param string $institution_code
     * @return array
     */
    public static function getNoticeDetails($id, $institution_code)
    {
        //查询SQL
        $sql = "select * from t_notice where id=:id and institution_code=:institution_code ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':institution_code', $institution_code);
        //执行获取结果
        $inst_0 = $cmd->queryAll();
        if ($inst_0 != false) {
            return $inst_0[0];
        } else {
            return $inst_0;
        }
    }

    /**
     * 通知公告数据新增 修改数据保存 （当$id为空是默认为新增 不为空时为修改）
     * @param int $id [null/int]
     * @param string $institution_code
     * @param string $title
     * @param int $category_id
     * @param string $content_html
     * @param string $description
     * @param int $is_shown [0/1]
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function noticeSave($id, $institution_code, $title, $category_id, $content_html, $description, $is_shown)
    {
        $sql = "call p_save_notice(:in_id,:in_institution_code,:in_title,:in_category_id,:in_content_html,:in_description,:in_is_shown,@oid)";

        $cmd = \Yii::$app->db->createCommand($sql);
        if ($id == null || $id == '') {
            $id = '0';
        }
        $cmd->bindValue(':in_id', $id);
        $cmd->bindValue(':in_institution_code', $institution_code);
        $cmd->bindValue(':in_title', $title);
        $cmd->bindValue(':in_category_id', $category_id);
        $cmd->bindValue(':in_content_html', $content_html);
        $cmd->bindValue(':in_description', $description);
        $cmd->bindValue(':in_is_shown', $is_shown);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @oid');
        $oid = $cmd->queryScalar();

        return $oid;

    }

    /**
     * 根据通知公告ID删除数据
     * @param int $id
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function noticeDelete($id)
    {
        if ($id != null && $id != '') {
            $cmd = \Yii::$app->db->createCommand('delete from t_notice where id=:id');
            $cmd->bindValue(':id', $id);
            return $cmd->execute();
        } else {
            return false;
        }
    }

    /*************通知公告分类********/
    /**
     * 查询通知公告分类列表(机构分类与系统平台分类)
     * @param string $institution_code
     * @return array|null
     */
    public static function getNoticeCategoryAndSYSList($institution_code)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "";
            if ($institution_code != '0') {
                $sql = "select * from t_notice_category where institution_code = '0' or institution_code=:institution_code ";
            } else {
                $sql = "select * from t_notice_category where institution_code=:institution_code ";
            }
            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 查询通知公告分类列表
     * @param string $institution_code
     * @return array|null
     */
    public static function getNoticeCategoryList($institution_code)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "select * from t_notice_category where institution_code='0' or institution_code=:institution_code ";
            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 根据机构编号和通知公告分类ID查询分类详情
     * @param int $id
     * @param string $institution_code
     * @return array
     */
    public static function getNoticeCategoryDetails($id, $institution_code)
    {
        //查询SQL
        $sql = "select * from t_notice_category  where id=:id and institution_code=:institution_code ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':institution_code', $institution_code);
        //执行获取结果
        $inst_0 = $cmd->queryAll();
        if ($inst_0 != false) {
            return $inst_0[0];
        } else {
            return $inst_0;
        }
    }

    /**
     * 通知公告分类数据新增 修改数据保存 （当$id为空是默认为新增 不为空时为修改）
     * @param int $id [null/int]
     * @param string $institution_code
     * @param string $name
     * @param string $identifier_code
     * @param text $description
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function noticeCategorySave($id, $institution_code, $name, $identifier_code, $description)
    {
        $sql = "call p_save_notice_category(:in_id,:in_institution_code,:in_name,:in_identifier_code,:in_description,@oid)";

        $cmd = \Yii::$app->db->createCommand($sql);
        if ($id == null || $id == '') {
            $id = '0';
        }
        $cmd->bindValue(':in_id', $id);
        $cmd->bindValue(':in_institution_code', $institution_code);
        $cmd->bindValue(':in_name', $name);
        $cmd->bindValue(':in_identifier_code', $identifier_code);
        $cmd->bindValue(':in_description', $description);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @oid');
        $oid = $cmd->queryScalar();

        return $oid;

    }

    /**
     * 根据通知公告分类ID删除数据
     * @param int $id
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function noticeCategoryDelete($id)
    {
        if ($id != null && $id != '') {
            $cmd = \Yii::$app->db->createCommand('delete from t_notice_category where id=:id');
            $cmd->bindValue(':id', $id);
            return $cmd->execute();
        } else {
            return false;
        }
    }
    /*************通知公告分类********/
    /*************通知公告********************************************************************************************/

    /*************友情链接********************************************************************************************/
    /**
     * 查询友情链接总数
     * @param string $institution_code
     * @param string $name
     * @param int $is_shown [0/1]
     * @return false|null|string
     */
    public static function getFriendlyLinkCountByfilters($institution_code, $name, $is_shown)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {
            $filterSQL = '';
            //添加查询条件
            if ($name != null && $name != '') {
                $filterSQL = $filterSQL . " and name like :filter_name ";
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $filterSQL = $filterSQL . " and is_shown = :filter_is_shown ";
            }
            //查询SQL
            $sql = "select count(id) from t_friendly_link where institution_code=:institution_code $filterSQL ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            //绑定参数
            if ($name != null && $name != '') {
                $filter_name = '%' . $name . '%';
                $cmd->bindValue(':filter_name', $filter_name);
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $cmd->bindValue(':filter_is_shown', $is_shown);
            }
            //执行获取结果
            $result = $cmd->queryScalar();
        }
        return $result;
    }

    /**
     * 查询友情链接列表
     * @param string $institution_code
     * @param string $name
     * @param int $is_shown [0/1]
     * @param int $page
     * @param int $pagesize
     * @return array|null
     */
    public static function getFriendlyLinkListByfilters($institution_code, $name, $is_shown, $page = 1, $pagesize = 10)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {
            $filterSQL = '';

            //添加查询条件
            if ($name != null && $name != '') {
                $filterSQL = $filterSQL . " and t.name like :filter_name ";
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $filterSQL = $filterSQL . " and t.is_shown = :filter_is_shown ";
            }

            //计算起始数据行
            if ($page <= 0) $page = 0;
            $start = ($page - 1) * $pagesize;
            //查询SQL
            $sql = "select t.*,ac.name as category_name from t_friendly_link t left join t_friendly_link_category ac on ac.id = t.category_id where t.institution_code=:institution_code $filterSQL LIMIT $start,$pagesize ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            //绑定参数
            if ($name != null && $name != '') {
                $filter_name = '%' . $name . '%';
                $cmd->bindValue(':filter_name', $filter_name);
            }
            if ($is_shown != null && $is_shown != '' && $is_shown != '-1') {
                $cmd->bindValue(':filter_is_shown', $is_shown);
            }
            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 根据机构编号和友情链接ID查询详情
     * @param int $id
     * @param string $institution_code
     * @return array
     */
    public static function getFriendlyLinkDetails($id, $institution_code)
    {
        //查询SQL
        $sql = "select * from t_friendly_link where id=:id and institution_code=:institution_code ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':institution_code', $institution_code);
        //执行获取结果
        $inst_0 = $cmd->queryAll();
        if ($inst_0 != false) {
            return $inst_0[0];
        } else {
            return $inst_0;
        }
    }

    /**
     * 友情链接数据新增 修改数据保存 （当$id为空是默认为新增 不为空时为修改）
     * @param int $id [null/int]
     * @param string $institution_code
     * @param string $name
     * @param int $category_id
     * @param string $link_url
     * @param int $is_shown [0/1]
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function friendlyLinkSave($id, $institution_code, $name, $category_id, $link_url, $is_shown)
    {
        $sql = "call p_save_friendly_link(:in_id,:in_institution_code,:in_name,:in_category_id,:in_link_url,:in_is_shown,@oid)";

        $cmd = \Yii::$app->db->createCommand($sql);
        if ($id == null || $id == '') {
            $id = '0';
        }
        $cmd->bindValue(':in_id', $id);
        $cmd->bindValue(':in_institution_code', $institution_code);
        $cmd->bindValue(':in_name', $name);
        $cmd->bindValue(':in_category_id', $category_id);
        $cmd->bindValue(':in_link_url', $link_url);
        $cmd->bindValue(':in_is_shown', $is_shown);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @oid');
        $oid = $cmd->queryScalar();

        return $oid;

    }

    /**
     * 根据友情链接ID删除数据
     * @param int $id
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function friendlyLinkDelete($id)
    {
        if ($id != null && $id != '') {
            $cmd = \Yii::$app->db->createCommand('delete from t_friendly_link where id=:id');
            $cmd->bindValue(':id', $id);
            return $cmd->execute();
        } else {
            return false;
        }
    }

    /*************通知公告分类********/
    /**
     * 查询友情链接分类列表(机构分类与系统平台分类)
     * @param string $institution_code
     * @return array|null
     */
    public static function getFriendlyLinkCategoryAndSYSList($institution_code)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "";
            if ($institution_code != '0') {
                $sql = "select * from t_friendly_link_category where institution_code = '0' or institution_code=:institution_code ";
            } else {
                $sql = "select * from t_friendly_link_category where institution_code=:institution_code ";
            }
            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 查询友情链接分类列表
     * @param string $institution_code
     * @return array|null
     */
    public static function getFriendlyLinkCategoryList($institution_code)
    {

        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "select * from t_friendly_link_category where institution_code='0' or institution_code=:institution_code ";
            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 根据机构编号和友情链接分类ID查询分类详情
     * @param int $id
     * @param string $institution_code
     * @return array
     */
    public static function getFriendlyLinkCategoryDetails($id, $institution_code)
    {
        //查询SQL
        $sql = "select * from t_friendly_link_category  where id=:id and institution_code=:institution_code ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':institution_code', $institution_code);
        //执行获取结果
        $inst_0 = $cmd->queryAll();
        if ($inst_0 != false) {
            return $inst_0[0];
        } else {
            return $inst_0;
        }
    }

    /**
     * 友情链接分类数据新增 修改数据保存 （当$id为空是默认为新增 不为空时为修改）
     * @param int $id [null/int]
     * @param string $institution_code
     * @param string $name
     * @param string $identifier_code
     * @param text $description
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function friendlyLinkCategorySave($id, $institution_code, $name, $identifier_code, $description)
    {
        $sql = "call p_save_friendly_link_category(:in_id,:in_institution_code,:in_name,:in_identifier_code,:in_description,@oid)";

        $cmd = \Yii::$app->db->createCommand($sql);
        if ($id == null || $id == '') {
            $id = '0';
        }
        $cmd->bindValue(':in_id', $id);
        $cmd->bindValue(':in_institution_code', $institution_code);
        $cmd->bindValue(':in_name', $name);
        $cmd->bindValue(':in_identifier_code', $identifier_code);
        $cmd->bindValue(':in_description', $description);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @oid');
        $oid = $cmd->queryScalar();

        return $oid;

    }

    /**
     * 根据友情链接分类ID删除数据
     * @param int $id
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function friendlyLinkCategoryDelete($id)
    {
        if ($id != null && $id != '') {
            $cmd = \Yii::$app->db->createCommand('delete from t_friendly_link_category where id=:id');
            $cmd->bindValue(':id', $id);
            return $cmd->execute();
        } else {
            return false;
        }
    }
    /*************友情链接分类********/
    /*************友情链接********************************************************************************************/


    /**
     * 前端商城获取广告列表 (根据广告分类定义标识符取得广告列表)
     * @param string $institution_code
     * @param string $identifier_code
     * @return array|null
     */
    public static function getAdListByIdentifierCode($institution_code, $identifier_code)
    {

        $result = null;
        if (($institution_code != null && $institution_code != '') && ($identifier_code != null && $identifier_code != '')) {
            //查询SQL
            $sql = "select t.*,ac.name as category_name from t_ad t left join t_ad_category ac on ac.id = t.category_id where t.institution_code=:institution_code and ac.identifier_code=:identifier_code ";
            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            $cmd->bindValue(':identifier_code', $identifier_code);
            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }


    /**
     * 同步广告种类到下级机构
     * @param $ins_code
     * @param $under_ins_codes
     * @return bool
     */
    public static function sysUnderInstitutionAdCategory($ins_code, $under_ins_codes)
    {
        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();

        try {
            //当前机构的广告种类
            $sql = 'SELECT * FROM t_ad_category WHERE institution_code = :institution_code';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $ins_code);
            $items = $cmd->queryAll();

            foreach ($under_ins_codes as $under_ins_code) {
                //删除下级机构的广告种类
                $sql = 'DELETE FROM t_ad_category WHERE institution_code = :institution_code';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':institution_code', $under_ins_code);
                $cmd->execute();

                //同步上级广告种类给下级机构
                foreach ($items as $item) {
                    $sql = 'INSERT INTO t_ad_category(id,name,identifier_code,institution_code,description)
                                VALUES (:id,:name,:identifier_code,:institution_code,:description)';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':id', Tools::db_uuid());
                    $cmd->bindValue(':name', $item['name']);
                    $cmd->bindValue(':identifier_code', $item['identifier_code']);
                    $cmd->bindValue(':institution_code', $under_ins_code);
                    $cmd->bindValue(':description', $item['description']);

                    $cmd->execute();
                }
            }

            $result = 1;
            $transaction->commit();


        } catch (\Exception $e) {
            $result = 0;
            $transaction->rollBack();
        }
        return $result > 0;
    }

    /**
     * 同步下级机构广告
     * @param $ins_code
     * @param $under_ins_codes
     * @return bool
     */
    public static function sysUnderInstitutionAd($ins_code, $under_ins_codes)
    {
        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();

        try {
            //当前机构的广告
            $sql = 'SELECT t1.*,t2.name AS category_name FROM t_ad t1 INNER JOIN t_ad_category t2 ON t2.id = t1.category_id WHERE t1.institution_code = :institution_code';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $ins_code);
            $items = $cmd->queryAll();

            foreach ($under_ins_codes as $under_ins_code) {
                //删除下级机构的广告
                $sql = 'DELETE FROM t_ad WHERE institution_code = :institution_code';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':institution_code', $under_ins_code);
                $cmd->execute();

                //同步上级广告给下级机构
                foreach ($items as $item) {
                    //获取新的广告种类
                    $sql = 'SELECT * FROM t_ad_category WHERE (institution_code = :institution_code OR institution_code = \'0\') AND name = :name';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':institution_code', $under_ins_code);
                    $cmd->bindValue(':name', $item['category_name']);
                    $category = $cmd->queryOne();
                    if ($category != false) {
                        $sql = 'INSERT INTO t_ad(id,institution_code,name,image_url,link_url,description,is_shown,category_id,create_datetime)
                                    VALUES (:id,:institution_code,:name,:image_url,:link_url,:description,:is_shown,:category_id,now())';
                        $cmd = \Yii::$app->db->createCommand($sql);
                        $cmd->bindValue(':id', Tools::db_uuid());
                        $cmd->bindValue(':institution_code', $under_ins_code);
                        $cmd->bindValue(':name', $item['name']);
                        $cmd->bindValue(':image_url', $item['image_url']);
                        $cmd->bindValue(':link_url', $item['link_url']);
                        $cmd->bindValue(':description', $item['description']);
                        $cmd->bindValue(':is_shown', $item['is_shown']);
                        $cmd->bindValue(':category_id', $category['id']);

                        $cmd->execute();
                        $result = 1;
                    } else {

                        //无对应的广告种类，应该先同步广告种类
                        $result = 0;
                        break;
                    }
                }
            }


            if ($result == 1) {
                $transaction->commit();
            } else {
                $transaction->rollBack();
            }

        } catch (\Exception $e) {
            $result = 0;
            $transaction->rollBack();
        }
        return $result > 0;
    }
}